package cn.bdqn.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.bdqn.bean.Bill;
import cn.bdqn.dao.BaseDao;
import cn.bdqn.dao.BillDao;

public class BillDaoImpl implements BillDao {

	/**
	 * 增加账单
	 */
	@Override
	public int addBill(Connection con, Bill bill) throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "insert into bill (id, billCode, productName, productDesc,"
				+ " productUnit, productAmount, "
				+ " totalPrice, isPay, createDate, createBy,providerId, isDelete ) "
				+ " values (sq_bill_id.nextval,?,?,?,?,?,?,?,sysdate,?,?,0) ";
		
		//执行sql语句
		Object [] params = { bill.getBillCode(), bill.getProductName(), bill.getProductDesc(),
				bill.getProductUnit(), bill.getProductAmount(), bill.getTotalPrice(),
				bill.getIsPay(), bill.getCreateBy(),bill.getProviderId()};
		
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}

	/**
	 * 修改账单
	 */
	@Override
	public int updateBill(Connection con, Bill bill) throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "update bill set billCode=?, productName=?, productDesc=?, "
				+ " productUnit=?, productAmount=?, "
				+ " totalPrice=?, providerId=?, isPay=?, modifyDate=sysdate, modifyBy=? where id=? ";
		
		//执行sql语句
		Object [] params = { bill.getBillCode(), bill.getProductName(), bill.getProductDesc(),
				bill.getProductUnit(), bill.getProductAmount(), bill.getTotalPrice(), 
				bill.getProviderId(), bill.getIsPay(), bill.getModifyBy(), bill.getId()};
		
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}

	/**
	 * 删除账单
	 * 逻辑删除
	 */
	@Override
	public int deleteBill(Connection con, Bill bill) throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "update bill set isDelete=1, modifyDate=sysdate, "
				+ " modifyBy=? where id=?";
		//执行sql语句
		Object [] params = { bill.getModifyBy(), bill.getId()};
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}

	/**
	 * 获取总记录数
	 */
	@Override
	public int getCount(Connection con, Bill bill) throws SQLException {
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count from bill where isDelete=0 ");
		ArrayList <Object> paraList = new ArrayList<Object>();
		//根据查询条件拼接sql语句并动态添加参数
		if(bill.getProductName()!=null && bill.getProductName() !=null ){
			sql.append(" and productName like ?");
			paraList.add("%"+ bill.getProductName()+"%");
		}
		if(bill.getProviderId() !=null){
			sql.append(" and providerId=?");
			paraList.add(bill.getProviderId());
		}
		if(bill.getIsPay()!=null){
			sql.append(" and isPay=?");
			paraList.add(bill.getIsPay());
		}
		//将StringBuffer 和list转换回来
		Object[] params = paraList.toArray();
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		//结果只有一行数据
		if(rs.next()){
			count = rs.getInt(1);
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return count;
	}

	@Override
	public List<Bill> getList(Connection con, Bill bill, int pageIndex, int pageSize)
			throws SQLException {
		List<Bill> list = new ArrayList<Bill>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		//sql分页前半截
		sql.append("SELECT * FROM  (SELECT ROWNUM rn, t1.* FROM "
				+ " (select b.*, provName from bill b left join provider p "
				+ " on b.providerId=p.id where b.isDelete=0 and p.isDelete=0 ") ;
		
		ArrayList <Object> paraList = new ArrayList<Object>();
		//根据查询条件拼接sql语句并动态添加参数
		if(bill.getProductName()!=null && bill.getProductName() !="" ){
			sql.append(" and productName like ? ");
			paraList.add("%"+ bill.getProductName()+"%");
		}
		if(bill.getProviderId() !=null){
			sql.append(" and providerId=? ");
			paraList.add(bill.getProviderId());
		}
		if(bill.getIsPay()!=null){
			sql.append(" and isPay=? ");
			paraList.add(bill.getIsPay());
		}
		//继续拼接分页查询后半截语句
		sql.append(" ORDER BY b.id) t1 WHERE ROWNUM <=?) t2 WHERE rn>=?") ;
		//处理分页参数
		int startWith = (pageIndex -1)  * pageSize + 1;
		int endWith = pageSize * pageIndex;
		//把分页参数赋值给paraList
		paraList.add(endWith);
		paraList.add(startWith);
		//将StringBuffer 和list转换回来
		Object[] params = paraList.toArray();
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		//查询结果
		while(rs.next()){
			Bill bi = new Bill();
			bi.setBillCode(rs.getString("billCode"));
			bi.setId(rs.getInt("id"));
			bi.setProductName(rs.getString("productName"));
			bi.setProductDesc(rs.getString("productDesc"));
			bi.setProductAmount(rs.getInt("productAmount"));
			bi.setProviderId(rs.getInt("providerId"));
			bi.setIsPay(rs.getInt("isPay"));
			bi.setProductUnit(rs.getString("productUnit"));
			bi.setProviderName(rs.getString("provName"));
			bi.setTotalPrice(rs.getBigDecimal("totalPrice").doubleValue());
			bi.setCreateDate(rs.getTimestamp("createDate"));
			list.add(bi);
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return list;
	}

	@Override
	public Bill getBillById(Connection con, int id) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Bill bill = new Bill();
		
		String sql = "select b.*, provName from bill b left join provider p "
				+ " on b.providerId=p.id where b.isDelete=0 and p.isDelete=0 and b.id=? " ;
		Object [] params = { id };
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		//查询结果
		if(rs.next()){
			bill.setBillCode(rs.getString("billCode"));
			bill.setId(rs.getInt("id"));
			bill.setProductName(rs.getString("productName"));
			bill.setProductDesc(rs.getString("productDesc"));
			bill.setProductAmount(rs.getInt("productAmount"));
			bill.setProviderId(rs.getInt("providerId"));
			bill.setIsPay(rs.getInt("isPay"));
			bill.setProductUnit(rs.getString("productUnit"));
			bill.setProviderName(rs.getString("provName"));
			bill.setTotalPrice(rs.getBigDecimal("totalPrice").doubleValue());
			bill.setCreateDate(rs.getTimestamp("createDate"));
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return bill;
	}

}
